package dao.impl.StoreDaoImpl;

import common.model.Entity.StoreEntity.CartItem;
import dao.StoreDao.CartItemDao;
import common.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Optional;

/**
 * 购物车项数据访问接口实现类，提供购物车项相关的数据库操作实现。
 *
 * <p>所有“删除”均为逻辑删除（is_deleted = 1）。
 * 查询操作默认过滤已逻辑删除的记录。
 *
 * @author your-name
 * @since 1.0
 */
public class CartItemDaoImpl implements CartItemDao {

    private static final Logger logger = LoggerFactory.getLogger(CartItemDaoImpl.class);
    private final QueryRunner queryRunner = new QueryRunner();

    /**
     * 根据购物车 ID 查询未逻辑删除的购物车项列表，按创建时间倒序排列。
     *
     * @param cartId 购物车主键
     * @return 购物车项列表；若未查询到数据或发生异常，返回空列表
     */
    @Override
    public List<CartItem> findByCart(Long cartId) {
        String sql = "SELECT id, cart_id AS cartId, product_id AS productId, quantity, price, " +
                "is_deleted AS isDeleted, created_at AS createdAt, updated_at AS updatedAt " +
                "FROM t_cart_item WHERE cart_id = ? AND is_deleted = 0 ORDER BY created_at DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(CartItem.class), cartId);
        } catch (SQLException e) {
            logger.error("根据购物车ID查询购物车项失败: cartId={}", cartId, e);
            return List.of();
        }
    }

    /**
     * 根据购物车 ID 和商品 ID 查询未逻辑删除的购物车项。
     *
     * @param cartId    购物车主键
     * @param productId 商品主键
     * @return 如果存在返回购物车项，否则返回 {@link Optional#empty()}
     */
    @Override
    public Optional<CartItem> findByCartAndProduct(Long cartId, Long productId) {
        String sql = "SELECT id, cart_id AS cartId, product_id AS productId, quantity, price, " +
                "is_deleted AS isDeleted, created_at AS createdAt, updated_at AS updatedAt " +
                "FROM t_cart_item WHERE cart_id = ? AND product_id = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            CartItem cartItem = queryRunner.query(conn, sql, new BeanHandler<>(CartItem.class), cartId, productId);
            return Optional.ofNullable(cartItem);
        } catch (SQLException e) {
            logger.error("根据购物车ID和商品ID查询购物车项失败: cartId={}, productId={}", cartId, productId, e);
            return Optional.empty();
        }
    }

    /**
     * 保存新的购物车项。
     *
     * @param cartItem 购物车项实体，需包含 cartId、productId、quantity、price
     * @return 保存成功返回 true，否则返回 false
     */
    @Override
    public boolean save(CartItem cartItem) {
        String sql = "INSERT INTO t_cart_item (cart_id, product_id, quantity, price) VALUES (?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection()) {
            int affected = queryRunner.update(conn, sql,
                    cartItem.getCartId(),
                    cartItem.getProductId(),
                    cartItem.getQuantity(),
                    cartItem.getPrice());
            return affected > 0;
        } catch (SQLException e) {
            logger.error("保存购物车项失败: cartId={}, productId={}",
                    cartItem.getCartId(), cartItem.getProductId(), e);
            return false;
        }
    }

    /**
     * 更新购物车项数量，并刷新更新时间。
     *
     * @param cartItem 购物车项实体，需包含 id 和 quantity
     * @return 更新成功返回 true，否则返回 false
     */
    @Override
    public boolean update(CartItem cartItem) {
        String sql = "UPDATE t_cart_item SET quantity = ?, updated_at = CURRENT_TIMESTAMP " +
                "WHERE id = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            int affected = queryRunner.update(conn, sql,
                    cartItem.getQuantity(),
                    cartItem.getId());
            return affected > 0;
        } catch (SQLException e) {
            logger.error("更新购物车项失败: id={}", cartItem.getId(), e);
            return false;
        }
    }

    /**
     * 逻辑删除指定主键的购物车项。
     *
     * @param id 购物车项主键
     * @return 删除成功返回 true，否则返回 false
     */
    @Override
    public boolean deleteById(Long id) {
        String sql = "UPDATE t_cart_item SET is_deleted = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?";
        try (Connection conn = DBUtil.getConnection()) {
            int affected = queryRunner.update(conn, sql, id);
            return affected > 0;
        } catch (SQLException e) {
            logger.error("删除购物车项失败: id={}", id, e);
            return false;
        }
    }

    /**
     * 根据主键查询未逻辑删除的购物车项。
     *
     * @param id 购物车项主键
     * @return 如果存在返回购物车项，否则返回 {@link Optional#empty()}
     */
    @Override
    public Optional<CartItem> findById(Long id) {
        String sql = "SELECT id, cart_id AS cartId, product_id AS productId, quantity, price, " +
                "is_deleted AS isDeleted, created_at AS createdAt, updated_at AS updatedAt " +
                "FROM t_cart_item WHERE id = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            CartItem cartItem = queryRunner.query(conn, sql, new BeanHandler<>(CartItem.class), id);
            return Optional.ofNullable(cartItem);
        } catch (SQLException e) {
            logger.error("根据ID查询购物车项失败: id={}", id, e);
            return Optional.empty();
        }
    }

    /**
     * 查询所有未逻辑删除的购物车项，按创建时间倒序排列。
     *
     * @return 购物车项列表；若异常返回空列表
     */
    @Override
    public List<CartItem> findAll() {
        String sql = "SELECT id, cart_id AS cartId, product_id AS productId, quantity, price, " +
                "is_deleted AS isDeleted, created_at AS createdAt, updated_at AS updatedAt " +
                "FROM t_cart_item WHERE is_deleted = 0 ORDER BY created_at DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(CartItem.class));
        } catch (SQLException e) {
            logger.error("查询所有购物车项失败", e);
            return List.of();
        }
    }

    /**
     * 统计未逻辑删除的购物车项数量。
     *
     * @return 数量；若异常返回 0
     */
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM t_cart_item WHERE is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new ScalarHandler<>());
        } catch (SQLException e) {
            logger.error("统计购物车项数量失败", e);
            return 0;
        }
    }
}